package cc.idoone.spring_boot_hello.datasource;

import cc.idoone.spring_boot_hello.datasource.jpa1.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @auther hemj
 * @since 2017/9/24
 */
@Repository
public class JdbcTemplateDao {

	@Autowired
	@Qualifier("jdbcTemplate1")
	private JdbcTemplate jdbcTemplate;

	public void createTable() {
		jdbcTemplate.update("CREATE TABLE IF NOT EXISTS user(\n" +
				"  id INTEGER not null primary key,\n" +
				"  name char(20),\n" +
				"  age INTEGER\n" +
				")");
		jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", 1, "a1", 1);
		jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", 2, "a2", 2);
		jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", 3, "a3", 3);
		jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", 4, "a4", 3);
		jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", 5, "a5", 3);
	}

	public int insert(int id, String name, int age) {
		return jdbcTemplate.update("insert into user(id,name,age) values(?, ?, ?)", id, name, age);
	}

	public List<User> findAll() {
		return jdbcTemplate.query("select id, name, age from user", new UserRowMapper());
	}

}
class UserRowMapper implements RowMapper<User> {

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User staff = new User();
		staff.setId(rs.getLong("id"));
		staff.setName(rs.getString("name"));
		staff.setAge(rs.getInt("age"));

		return staff;
	}
}
